package com.adou.service.impl;

import com.adou.common.dto.InConditionDto;
import com.adou.common.dto.SearchConditionOutDto;
import com.adou.common.mapper.*;
import com.adou.common.model.DefForm;
import com.adou.common.model.RunKeyForm;
import com.adou.common.model.SearchCondition;
import com.adou.common.model.SearchLoginFormKey;
import com.adou.common.util.StringUtil;
import com.adou.common.util.UtilPath;
import com.adou.service.FormSearchService;
import com.adou.service.impl.base.BaseServiceImp;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.FileOutputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Service("FormSearchService")
public class FormSearchServiceImp extends BaseServiceImp<SearchCondition> implements FormSearchService {

    @Autowired
    private SearchConditionMapper dao;
    @Autowired
    private DefFormMapper formdao;
    //    @Autowired
//    private DefFieldMapper fielddao;
    @Autowired
    private SearchLoginFormMapper logindao;
    @Autowired
    private RunDynXxxMapper dyndao;

    @Autowired
    private RunKeyFormMapper runKeyFormMapper;

    @Autowired
    HttpServletRequest request;

    @Override
    @Transactional
    public int insertSearchCondition(SearchCondition searchCondition, String fids) {
        int res = 0;
        String type = searchCondition.getCtype();
        String findfield1 = searchCondition.getCfindfield1();
        String condition = searchCondition.getCcondition();
        if(!StringUtil.isNotBlank(type)){
            return 211;
        }
        if(!StringUtil.isNotBlank(findfield1)){
            return 212;
        }
        if(!StringUtil.isNotBlank(condition)){
            return 213;
        }
        if(!(dao.count(searchCondition) == 0)){
            return 214;
        }
        if(fids == null){
            return 215;
        }
        if(!StringUtil.isNumeric(fids.replaceAll(",", ""))){
            return 216;
        }
        res = dao.insert(searchCondition);
        fids = StringUtil.replaceStartEnd(fids, ",");
        int count = intoLoginForm(fids, searchCondition.getId());
        if (count == 0) {
            dao.delete(searchCondition);
            res = 0;
        }
        return res;
    }

    @Override
    @Transactional
    public Map<String, Object> findSearchCondition(SearchCondition searchCondition) {
        Map<String, Object> resultMap = new HashMap<>();
        List<SearchCondition> list = dao.find(searchCondition);
        if (list != null && list.size() > 0) {
            SearchConditionOutDto searchConditionOutDto = new SearchConditionOutDto(list.get(0));
            searchConditionOutDto.setSearchLoginFormOutDtoList(logindao.findBySid(searchCondition.getId()));
            resultMap.put("res", 1);
            resultMap.put("data", searchConditionOutDto);
        } else {
            resultMap.put("res", 0);
        }
        return resultMap;
    }


    @Override
    @Transactional
    public int updateSearchCondition(SearchCondition searchCondition, String fids) {
        int res = 0;
        String type = searchCondition.getCtype();
        String findfield1 = searchCondition.getCfindfield1();
        String condition = searchCondition.getCcondition();
        if(!StringUtil.isNotBlank(type)){
            return 211;
        }
        if(!StringUtil.isNotBlank(findfield1)){
            return 212;
        }
        if(!StringUtil.isNotBlank(condition)){
            return 213;
        }
        if(!(dao.count(searchCondition) == 0)){
            return 214;
        }
        if(fids == null){
            return 215;
        }
        if(!StringUtil.isNumeric(fids.replaceAll(",", ""))){
            return 216;
        }
        logindao.deleteBySid(searchCondition.getId());
        fids = StringUtil.replaceStartEnd(fids, ",");
        int count = intoLoginForm(fids, searchCondition.getId());
        if (count > 0) {
            res = dao.update(searchCondition);
        }
        return res;
    }

    private int intoLoginForm(String fids, Long sid) {
        String[] idArray;
        int count = 0;
        if (fids.contains(",")) {
            idArray = fids.split(",");
        } else {
            idArray = new String[]{fids};
        }
        SearchCondition searchCondition = new SearchCondition();
        searchCondition.setId(sid);
        List<SearchCondition> searchConditionList = dao.find(searchCondition);
        if (searchConditionList == null || searchConditionList.size() == 0) {
            return count;
        }
        searchCondition = searchConditionList.get(0);
        for (String ids : idArray) {
            Long fid = new Long(ids);
            //判断表单中是否含查询字段
            DefForm defForm = new DefForm();
            defForm.setId(fid);
            List<DefForm> defFormList = formdao.find(defForm);
            if (defFormList != null && defFormList.size() > 0) {
                defForm = defFormList.get(0);
                if (defForm.getBflag()) {
                    if (defForm.getIcrttype() == 0) {
                        String tablename = defForm.getCformname() + defForm.getIver();
                        String dbname = dyndao.getCurDataBaseName();
                        List<Map<String, Object>> mapList = dyndao.findAll(StringUtil.getSysFiledsByPara(dbname, tablename, "COLUMN_NAME"));
                        Map<String, Object> commap1 = new HashMap<>();
                        commap1.put("COLUMN_NAME", searchCondition.getCfindfield1());
                        if (mapList.contains(commap1)) {
                            Map<String, Object> commap2 = new HashMap<>();
                            commap2.put("COLUMN_NAME", searchCondition.getCfindfield2());
                            if (searchCondition.getCfindfield2() == null || searchCondition.getCfindfield2().equals("") ||
                                    (!searchCondition.getCfindfield2().equals("") && mapList.contains(commap2))) {
                                SearchLoginFormKey searchLoginFormKey = new SearchLoginFormKey();
                                searchLoginFormKey.setIfid(fid);
                                searchLoginFormKey.setIsid(sid);
                                count += logindao.insert(searchLoginFormKey);
                            }
                        }
                    } else {
                        //键值对处理
                    }
                }
            }
        }
        return count;
    }

    @Override
    @Transactional
    public int deleteSearchCondition(SearchCondition searchCondition) {
        int res = 0;
        if (searchCondition.getId() != null) {
            logindao.deleteBySid(searchCondition.getId());
            res = dao.delete(searchCondition);
        }
        return res;
    }

    @Override
    @Transactional
    public Map<String, Object> getSearchCondition(Long ifid) {
        Map<String, Object> resultMap = new HashMap<>();
        if (ifid == null) {
            resultMap.put("res", 0);
            return resultMap;
        }
        List<SearchCondition> searchConditionList = dao.getSearchCondition(ifid);
        if (searchConditionList != null && searchConditionList.size() > 0) {
            resultMap.put("res", 1);
            resultMap.put("data", searchConditionList);
        } else {
            resultMap.put("res", 0);
        }
        return resultMap;
    }

    @Override
    @Transactional
    public Map<String, Object> getSearchData(List<InConditionDto> inConditionDtoList, Long ifid,int pageNum, int pageSize, Boolean sysflag) {
        Map<String, Object> resultMap = new HashMap<>();
        List<Map<String, Object>> dataList = new ArrayList<>();
        DefForm defForm = new DefForm();
        if (ifid == null) {
            defForm.setBflag(true);
        } else {
            defForm.setId(ifid);
        }
        List<DefForm> defFormList = formdao.find(defForm);
        for (DefForm defForm1 : defFormList) {
            if (defForm1.getBflag()) {
                if (defForm1.getIcrttype() == 0) {
                    String search_sql = getSql(defForm1, inConditionDtoList);
                    if (inConditionDtoList != null && !inConditionDtoList.isEmpty() && (inConditionDtoList.size() <= 0 || search_sql.equals(""))) {
                        continue;
                    }
                    dataList.add(getData(defForm1, search_sql, pageNum, pageSize, sysflag));
                } else {
                    //键值对处理
                }
            }
        }
        if (dataList != null && dataList.size() > 0) {
            resultMap.put("res", 1);
            resultMap.put("data", dataList);
        } else {
            resultMap.put("res", 0);
        }
        return resultMap;
    }

    @Override
    @Transactional
    public Map<String, Object> outSearchData(List<InConditionDto> inConditionDtoList, Long ifid) {
        Map<String, Object> resultMap = new HashMap<>();
        Map<String, Object> dataMap = getSearchData(inConditionDtoList, ifid, 0,0,false);
        if ((int) dataMap.get("res") == 1) {
            String filePath = UtilPath.getRootPath() + "/upload/";
            File realpath = new File(filePath);
            if (!realpath.exists()) {
                realpath.mkdirs();
            }
            String fileName = UUID.randomUUID() + ".xls";
            filePath = UtilPath.getRootPath() + "/upload/" + fileName;
            File realFile = new File(filePath);
            try {
                if (!realFile.exists()) {
                    realFile.createNewFile();
                }
                FileOutputStream fos = new FileOutputStream(filePath);
                Workbook wb;
                if (filePath.endsWith(".xls")) {
                    wb = new HSSFWorkbook();
                } else {
                    wb = new XSSFWorkbook();
                }
                Sheet sheet = wb.createSheet();
                wb.setSheetName(0, "Sheet1");
                Row textrow;
                Cell textcell;
                List<Map<String, Object>> dataList = (List<Map<String, Object>>) dataMap.get("data");
                int r = 0;
                for (Map<String, Object> map : dataList) {
                    String title = (String) map.get("title");
                    Map<String, Object> m = (Map<String, Object>)map.get("data");
                    List<Map<String, Object>> data = (List<Map<String, Object>>) m.get("list");
                    if (data != null && data.size() > 0) {
                        sheet.addMergedRegion(new CellRangeAddress(0,0,0,data.get(0).size()-1));
                        textrow = sheet.createRow(r);
                        textcell = textrow.createCell(0);
                        textcell.setCellValue(title);

                        /**
                         * 样式设置
                         */
                        CellStyle cellStyle = wb.createCellStyle();
                        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
                        Font font = wb.createFont();
                        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                        font.setFontHeight((short)360);
                        cellStyle.setFont(font);
                        textcell.setCellStyle(cellStyle);

                        for (Map<String, Object> map1 : data) {
                            int c = 0;
                            textrow = sheet.createRow(r + 1);
                            Iterator<Map.Entry<String, Object>> it = map1.entrySet().iterator();
                            while (it.hasNext()) {
                                textcell = textrow.createCell(c);
                                Map.Entry<String, Object> entry = it.next();
                                textcell.setCellValue((String) entry.getValue());
                                c += 1;
                            }
                            r++;
                        }
                    }
                }
                wb.write(fos);
                fos.close();
                String url = request.getScheme() + "://" + request.getServerName() + ":" + request.getServerPort() + request.getContextPath();
                url = url + "/upload/" + fileName;
                resultMap.put("res", 1);
                resultMap.put("data", url);
            } catch (Exception ex) {
                resultMap.put("res ", 0);
            }
        } else {
            resultMap.put("res ", 0);
        }
        return resultMap;
    }

    @Override
    @Transactional
    public Map<String, Object> inDataToForm(Long ifid, MultipartFile file) {
        Map<String, Object> resultMap = new HashMap<>();
        if (ifid == null) {
            resultMap.put("res", 211);
            return resultMap;
        }
        DefForm defForm = new DefForm();
        defForm.setId(ifid);
        List<DefForm> defFormList = formdao.find(defForm);
        if (defFormList != null && defFormList.size() > 0 && file != null) {
            defForm = defFormList.get(0);
            if (!defForm.getBflag()) {
                resultMap.put("res", 212);
                return resultMap;
            }
            if (defForm.getIcrttype() == 0) {
                //动态表从系统表中获取字段
                String table_name = defForm.getCformname() + defForm.getIver();
                String dbname = dyndao.getCurDataBaseName();
                List<Map<String, Object>> mapList = dyndao.findAll(StringUtil.getSysFiledsByPara(dbname, table_name, "COLUMN_NAME,DATA_TYPE"));
                //导入excel
                try {
                    int count = 0;
                    String filename = file.getOriginalFilename();
                    Workbook wb = null;
                    if (filename.endsWith(".xls")) {
                        wb = new HSSFWorkbook(file.getInputStream());
                    } else if (filename.endsWith(".xlsx")) {
                        wb = new XSSFWorkbook(file.getInputStream());
                    }
                    if (wb != null) {
                        //读取excel并写入数据库
                        Sheet sheet = wb.getSheetAt(0);
                        Row textrow;
                        Cell textcell;
                        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
                            textrow = sheet.getRow(r);
                            String field_values = "", field_names = "";
                            if (textrow != null) {
                                int c = 0;
                                for (Map<String, Object> map : mapList) {
                                    textcell = textrow.getCell(c);
                                    field_names += map.get("COLUMN_NAME") + ",";
                                    switch ((String) map.get("DATA_TYPE")) {
                                        case "int":
                                        case "float":
                                            field_values += textcell.getCellType() == 0 ? textcell.getNumericCellValue() + "," : textcell.getStringCellValue() + ",";
                                            break;
                                        default:
                                            if(textcell.getCellType()==0 && HSSFDateUtil.isCellDateFormatted(textcell)){
                                                Date d = textcell.getDateCellValue();
                                                DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
                                                field_values += "'"+format.format(d)+"',";
                                            }
                                            else if(textcell.getCellType()==0 && HSSFCell.CELL_TYPE_NUMERIC == textcell.getCellType()){
                                                DecimalFormat format = new DecimalFormat("0");
                                                field_values += "'"+format.format(textcell.getNumericCellValue())+"',";
                                            }
                                            else{
                                                field_values += textcell.getCellType() == 0 ? "'" + textcell.getNumericCellValue() + "'," : "'" + textcell.getStringCellValue() + "',";
                                            }
                                            break;
                                    }
                                    c += 1;
                                }
                                if (!field_names.equals("") && !field_values.equals("")) {
                                    field_names = field_names + "iformId";
                                    field_values = field_values + defForm.getId();
                                    Map<String, Object> map = new HashMap<>();
                                    map.put("table_name", table_name);
                                    map.put("field_names", field_names);
                                    map.put("field_values", field_values);
                                    count += dyndao.insert(map);
                                }
                            }
                        }
                    }
                    resultMap.put("res", 1);
                    resultMap.put("data", count);
                } catch (Exception ex) {
                    resultMap.put("res", 0);
                }
            } else {
                //键值对处理
            }
        } else {
            resultMap.put("res", 0);
        }
        return resultMap;
    }

    @Override
    @Transactional(readOnly = true)
    public Map<String, Object> queryFields(String ids) {
        Map<String, Object> resultMap = new HashMap<>();
        if(ids == null){
            resultMap.put("result",103);
            resultMap.put("msg","主键为空");
            return resultMap;
        }
        if(!StringUtil.isNumeric(ids.replaceAll(",", ""))){
            resultMap.put("result",104);
            resultMap.put("msg","主键格式错误");
            return resultMap;
        }
        String[] idArr = ids.split(",");
        List<Map<String,Object>> list = runKeyFormMapper.queryFieldsByFormId(Long.parseLong(idArr[0]));
        if(idArr.length == 1){
            resultMap.put("data", list);
            return resultMap;
        }
        //多表联查
        List<Map<String, Object>> resList = new ArrayList<>();
        for (Map<String,Object> m : list) {
            int count = runKeyFormMapper.checkField(ids, m.get("cvalue").toString()
            ,m.get("cfieldName").toString());
            if(count == idArr.length){
                resList.add(m);
            }
        }
        if(resList.size()>0){
            resultMap.put("data", resList);
        }
        else {
            resultMap.put("result",105);
            resultMap.put("msg","列表为空");
        }
        return resultMap;
    }

    private Map<String, Object> getData(DefForm defForm, String search_sql, int pageNum, int pageSize, Boolean sysflag) {
        Map<String, Object> resultMap = new HashMap<>();
        //动态表从系统表中获取字段
        String field_names = sysflag ? "id,iformId," : "";
        String table_name = defForm.getCformname() + defForm.getIver();
        String dbname = dyndao.getCurDataBaseName();
        List<Map<String, Object>> mapList = dyndao.findAll(StringUtil.getSysFiledsByPara(dbname, table_name, "COLUMN_NAME"));
        if (mapList != null && mapList.size() > 0) {
            for (Map<String, Object> map : mapList) {
                field_names += map.get("COLUMN_NAME") + ",";
            }
            field_names = field_names.substring(0, field_names.length() - 1);
        }
        if (!table_name.equals("") && !field_names.equals("")) {
            Map<String, Object> para = new HashMap<>();
            para.put("field_names", field_names);
            para.put("table_name", table_name);
            search_sql += " AND ((SELECT fmf.id FROM ad_flowmainfile fmf WHERE fmf.id = iflowId) IS NULL OR ";
            search_sql += " (SELECT fmf.bflag FROM ad_flowmainfile fmf WHERE fmf.id = iflowId) = 1) ";
            para.put("search_sql", search_sql);
            Map<String, Object> data = new HashMap<>();
            if(sysflag) {
                Page<?> page = PageHelper.startPage(pageNum, pageSize);
                List<Map<String, Object>> mapList1 = dyndao.findAll(para);
                data.put("totalpage", page.getPages());
                data.put("totalcount", page.getTotal());
                data.put("pageNum", pageNum);
                data.put("pageSize", pageSize);
                data.put("list", mapList1);
            }else{
                List<Map<String, Object>> mapList1 = dyndao.findAll(para);
                data.put("totalpage", 0);
                data.put("totalcount", 0);
                data.put("pageNum", 0);
                data.put("pageSize", 0);
                data.put("list", mapList1);
            }
            resultMap.put("title", defForm.getCformdisplay());
            resultMap.put("search", search_sql);
            resultMap.put("data", data);
        }
        return resultMap;
    }

    private String getSql(DefForm defForm, List<InConditionDto> inConditionDtoList) {
        String search_sql = "";
        String tablename = defForm.getCformname() + defForm.getIver();
        String dbname = dyndao.getCurDataBaseName();
        List<Map<String, Object>> mapList = dyndao.findAll(StringUtil.getSysFiledsByPara(dbname, tablename, "COLUMN_NAME"));
        for (InConditionDto inConditionDto : inConditionDtoList) {
            SearchCondition searchCondition = new SearchCondition();
            searchCondition.setId(inConditionDto.getId());
            List<SearchCondition> searchConditionList = dao.find(searchCondition);
            if (searchConditionList != null && searchConditionList.size() > 0) {
                searchCondition = searchConditionList.get(0);
                Map<String, Object> commap1 = new HashMap<>();
                commap1.put("COLUMN_NAME", searchCondition.getCfindfield1());
                if (mapList.contains(commap1)) {
                    Map<String, Object> commap2 = new HashMap<>();
                    commap2.put("COLUMN_NAME", searchCondition.getCfindfield2());
                    if (searchCondition.getCfindfield2() == null || searchCondition.getCfindfield2().equals("") ||
                            (!searchCondition.getCfindfield2().equals("") && mapList.contains(commap2))) {
                        search_sql += joinSql(searchCondition, inConditionDto);
                    }
                }
            }
        }
        return search_sql;
    }


    private String joinSql(SearchCondition searchCondition, InConditionDto inConditionDto) {
        String compareData, search_sql = "";
        if (inConditionDto.getFindvalue1() != null) {
            switch (searchCondition.getCtype()) {
                case "numeric":
                    compareData = " " + inConditionDto.getFindvalue1() + " ";
                    break;
                default:
                    compareData = " '" + inConditionDto.getFindvalue1() + "' ";
                    break;
            }
            if (searchCondition.getCcondition() == null) {
                searchCondition.setCcondition("=");
            }
            switch (searchCondition.getCcondition()) {
                case "between":
                    search_sql += " and " + searchCondition.getCfindfield1() + " >= " + compareData;
                    break;
                case "like":
                    search_sql += " and locate(" + compareData + "," + searchCondition.getCfindfield1() + ")>0";
                    break;
                default:
                    search_sql += " and " + searchCondition.getCfindfield1() + " " + searchCondition.getCcondition() + " " + compareData;
                    break;
            }
        }
        if (searchCondition.getCcondition() != null && searchCondition.getCcondition().equals("between")) {
            if (inConditionDto.getFindvalue2() != null) {
                switch (searchCondition.getCtype()) {
                    case "numeric":
                        compareData = " " + inConditionDto.getFindvalue2() + " ";
                        search_sql += " and " + searchCondition.getCfindfield2() + "<= " + compareData;
                        break;
                    default:
                        compareData = " '" + inConditionDto.getFindvalue2() + "' ";
                        search_sql += " and " + searchCondition.getCfindfield2() + "<= " + compareData;
                        break;
                }

            }
        }
        return search_sql;
    }
}
